package com.pet.dao;

import com.pet.bean.Owner;
import com.pet.bean.Pet;
import com.pet.util.DataSourcesTool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PetDao {
    // 根据pet_id查询宠物
    public Pet select_PetByID(Connection con, int id) throws SQLException {
        String sql = "select * from pet where pet_id=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, id);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            int pet_id = resultSet.getInt("pet_id");
            String name1 = resultSet.getString("name");
            int store_id = resultSet.getInt("store_id");
            int owner_id = resultSet.getInt("owner_id");
            double worth = resultSet.getDouble("worth");
            String type = resultSet.getString("type");
            Pet pet = new Pet(pet_id, name1, store_id, owner_id, worth, type);
            return pet;
        }
        return null;
    }
    // 查询商店中待售卖的宠物
    public List<Pet> select_PetByPetstore(Connection con, int s_id) throws SQLException {
        List<Pet> list = new ArrayList<>();
        String sql = "select * from pet where store_id=? and owner_id is null";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, s_id);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            int pet_id = resultSet.getInt("pet_id");
            String name1 = resultSet.getString("name");
            int store_id = resultSet.getInt("store_id");
            int owner_id = resultSet.getInt("owner_id");
            double worth = resultSet.getDouble("worth");
            String type = resultSet.getString("type");
            Pet pet = new Pet(pet_id, name1, store_id, owner_id, worth, type);
            list.add(pet);
        }
        return list;
    }
    // 查询所有待售卖的宠物
    public List<Pet> select_AllPet(Connection con) throws SQLException {
        List<Pet> list = new ArrayList<>();
        String sql = "select * from pet where store_id is not null and owner_id is null";
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            int pet_id = resultSet.getInt("pet_id");
            String name1 = resultSet.getString("name");
            int store_id = resultSet.getInt("store_id");
            int owner_id = resultSet.getInt("owner_id");
            double worth = resultSet.getDouble("worth");
            String type = resultSet.getString("type");
            Pet pet = new Pet(pet_id, name1, store_id, owner_id, worth, type);
            list.add(pet);
        }
        return list;
    }
    // 通过主人id查询宠物
    public List<Pet> select_PetByOwner_id(Connection con, int o_id) throws SQLException {
        List<Pet> list = new ArrayList<>();
        String sql = "select * from pet where store_id is null and owner_id=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, o_id);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            int pet_id = resultSet.getInt("pet_id");
            String name1 = resultSet.getString("name");
            int store_id = resultSet.getInt("store_id");
            int owner_id = resultSet.getInt("owner_id");
            double worth = resultSet.getDouble("worth");
            String type = resultSet.getString("type");
            Pet pet = new Pet(pet_id, name1, store_id, owner_id, worth, type);
            list.add(pet);
        }
        return list;
    }
    // 通过商家id查询宠物
    public List<Pet> select_PetByStore_id(Connection con, int s_id) throws SQLException {
        List<Pet> list = new ArrayList<>();
        String sql = "select * from pet where store_id=? and owner_id is null";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, s_id);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            int pet_id = resultSet.getInt("pet_id");
            String name1 = resultSet.getString("name");
            int store_id = resultSet.getInt("store_id");
            int owner_id = resultSet.getInt("owner_id");
            double worth = resultSet.getDouble("worth");
            String type = resultSet.getString("type");
            Pet pet = new Pet(pet_id, name1, store_id, owner_id, worth, type);
            list.add(pet);
        }
        return list;
    }
    //修改宠物数据的操作
    public boolean updatePet(Connection con, Pet Pet) throws SQLException {
        // 2、定义sql语句
        String sql = "update Pet set name=?,type=?,store_id=?,owner_id=?,worth=? where pet_id=?;";
        // 3、创建sql语句对象、如果sql语句有使用占位符，则给占位符赋值
        PreparedStatement ps = con.prepareStatement(sql);
        // 赋值操作
        ps.setString(1, Pet.getName());
        ps.setString(2, Pet.getType());
        ps.setInt(3, Pet.getStore_id());
        ps.setInt(4, Pet.getOwner_id());
        ps.setDouble(5, Pet.getWorth());
        ps.setInt(6, Pet.getPet_id());
        // 4、通过sql语句对象来执行sql操作
        int result = ps.executeUpdate();
        // 5、判断影响行数
        if (result > 0) {
            // 返回
            return true;
        }
        // 如果没有就返回null
        return false;
    }
    //修改宠物主人的操作
    public boolean updatePetOwner(Connection con, Integer store_id, Integer owner_id, Integer pet_id) throws SQLException {
        // 2、定义sql语句
        String sql = "update Pet set store_id=?,owner_id=? where pet_id=?;";
        // 3、创建sql语句对象、如果sql语句有使用占位符，则给占位符赋值
        PreparedStatement ps = con.prepareStatement(sql);
        // 赋值操作
        ps.setObject(1, store_id);
        ps.setObject(2, owner_id);
        ps.setInt(3, pet_id);
        // 4、通过sql语句对象来执行sql操作
        int result = ps.executeUpdate();
        // 5、判断影响行数
        if (result > 0) {
            // 返回
            return true;
        }
        // 如果没有就返回null
        return false;
    }
    //培育宠物
    public boolean createPet(Connection con, String name, Object s_id, Object o_id, double worth, String type) throws SQLException {
        String sql = "insert into pet(name,store_id,owner_id,worth,type) values(?,?,?,?,?)";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, name);
        ps.setObject(2, s_id);
        ps.setObject(3, o_id);
        ps.setDouble(4, worth);
        ps.setString(5, type);
        int result = ps.executeUpdate();
        if (result > 0) {
            return true;
        }
        return false;
    }
}
